import ExcelJS from 'exceljs'
import FileSaver from 'file-saver'

export async function workbookToExcel(workbookData) {
  const workbook = new ExcelJS.Workbook()

  workbookData.sheetOrder.forEach(sheetId => {
    const sheetData = workbookData.sheets[sheetId]
    const worksheet = workbook.addWorksheet(sheetData.name)

    const columnConfigs = [];
    for (let col = 0; col < sheetData.columnCount; col++) {
      const colConfig = sheetData.columnData?.[col] || {};

      const width = colConfig.w ? (colConfig.w - 5) / 7 : 15;

      columnConfigs.push({
        width,
        hidden: colConfig.hd === 1
      });
    }

    worksheet.columns = columnConfigs;

    if (sheetData.rowData) {
      Object.entries(sheetData.rowData).forEach(([rowIndexStr, rowConfig]) => {
        const rowIndex = parseInt(rowIndexStr);
        const row = worksheet.getRow(rowIndex + 1);

        if (rowConfig.h) {
          row.height = rowConfig.h * 0.75;
        }
      });
    }

    const rowHeights = sheetData.rowHeaderData?.heights || [];
    rowHeights.forEach((height, index) => {
      worksheet.getRow(index + 1).height = height;
    });

    Object.entries(sheetData.cellData).forEach(([rowStr, cols]) => {
      const row = parseInt(rowStr)
      Object.entries(cols).forEach(([colStr, cell]) => {
        const col = parseInt(colStr)
        const excelCell = worksheet.getCell(row + 1, col + 1)

        excelCell.value = cell.v

        if (cell.s) {
          const style = workbookData.styles?.[cell.s]
          if (style) {
            excelCell.font = {
              name: style.ff || 'Calibri',
              size: style.fs || 11,
              bold: !!style.bl,
              italic: !!style.it,
              underline: style.ul?.s ? 'single' : 'none',
              color: { argb: convertToArgb(style.cl?.rgb) }
            };

            excelCell.alignment = {
              vertical: mapVerticalAlign(style.vt),
              horizontal: mapHorizontalAlign(style.ht),
              wrapText: style.tb === 3,
              indent: style.ti || 0
            };

            if (style.bg?.rgb) {
              excelCell.fill = {
                type: 'pattern',
                pattern: 'solid',
                fgColor: { argb: convertToArgb(style.bg.rgb) }
              };
            }

            excelCell.border = {
              right: { style: mapBorderStyle(style.bd?.r?.s), color: { argb: convertToArgb(style.bd?.r?.cl?.rgb) } },
              bottom: { style: mapBorderStyle(style.bd?.r?.s), color: { argb: convertToArgb(style.bd?.b?.cl?.rgb) } },
              left: { style: mapBorderStyle(style.bd?.r?.s), color: { argb: convertToArgb(style.bd?.l?.cl?.rgb) } },
              top: { style: mapBorderStyle(style.bd?.r?.s), color: { argb: convertToArgb(style.bd?.t?.cl?.rgb) } },
            }
          }
        }
      })
    })

    sheetData.mergeData?.forEach(merge => {
      worksheet.mergeCells(
        merge.startRow + 1,
        merge.startColumn + 1,
        merge.endRow + 1,
        merge.endColumn + 1
      )
    })
  })

  const buffer = await workbook.xlsx.writeBuffer()
  const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
  FileSaver.saveAs(blob, `${workbookData.name || 'export'}.xlsx`)
}

function convertToArgb(color) {
  if (!color) return 'FF000000';

  if (color.startsWith('#') && color.length === 4) {
    const r = color[1], g = color[2], b = color[3];
    return `FF${r}${r}${g}${g}${b}${b}`;
  }

  if (color.startsWith('#') && color.length === 7) {
    return `FF${color.slice(1)}`;
  }

  const rgbaMatch = color.match(/rgba?\((\d+),\s*(\d+),\s*(\d+)(,\s*[\d.]+)?\)/);
  if (rgbaMatch) {
    const r = parseInt(rgbaMatch[1]).toString(16).padStart(2, '0');
    const g = parseInt(rgbaMatch[2]).toString(16).padStart(2, '0');
    const b = parseInt(rgbaMatch[3]).toString(16).padStart(2, '0');
    return `FF${r}${g}${b}`;
  }

  return 'FF000000';
}

function mapVerticalAlign(align) {
  switch (align) {
    case 1: return 'top'
    case 2: return 'middle'
    case 3: return 'bottom'
    default: return 'middle'
  }
}

function mapHorizontalAlign(align) {
  switch (align) {
    case 1: return 'left'
    case 2: return 'center'
    case 3: return 'right'
    default: return 'left'
  }
}

function mapBorderStyle(style) {
  switch (style) {
    case 1: return 'thin'
    case 2: return 'hair'
    case 3: return 'dotted'
    case 4: return 'dashed'
    case 5: return 'dashDot'
    case 6: return 'dashDotDot'
    case 7: return 'double'
    case 8: return 'medium'
    case 9: return 'mediumDashed'
    case 10: return 'mediumDashDot'
    case 11: return 'mediumDashDotDot'
    case 12: return 'slantDashDot'
    case 13: return 'thick'
    default: return 'none'
  }
}
